package com.atguigu1;

import com.beans.UserTable;
import com.utils.JDBCUtilsJiuBan;
import org.junit.Test;

import java.lang.reflect.Field;
import java.sql.*;

/**
 * @auther user
 * @date 2021/12/9 - 3:34
 *      1 什么是数据库事务
 *      事务：一组逻辑操作单元，使数据库从一种状态变化到另一种状态
 *          一组逻辑操纵单元：一个或多个DML操作
 *          2 事务处理的原则
 *              要不执行成功，要不回滚
 *          3 数据一旦提交，就不可回滚
 *          4 哪些操作会导致数据库的自动提交
 *              DDL set autocommit = false对DDL无效
 *              DML 默认情况下，自动提交，可通过set autocommit = false 的方式取消自动提交
 *              关闭连接时默认会自动提交
 *
 *
 */
public class TransactionTest {
    //通用更新操作 -----Version 2.0 -----考虑事务
    public int update(Connection connection,String sql,Object ...args) throws Exception {
        PreparedStatement preparedStatement = null;
        int j = 0;
        try {
            //1 获取连接
            //2 预编译sql,返回preparedStatment实例
            preparedStatement = connection.prepareStatement(sql);
            //3 填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            //4 执行
            j = preparedStatement.executeUpdate();
            return j;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭资源
            JDBCUtilsJiuBan.closeResource(null,preparedStatement);
        }
        return 0;
    }
    @Test
    public void testUpdate() throws Exception{
        Connection connection = null;
        try {
            //获取连接
            connection = JDBCUtilsJiuBan.getConnection();
            //关闭自动提交
            connection.setAutoCommit(false);
            String sql = "update user_table set balance = balance-100 where user = ?";
            update(connection,sql,"AA");
            //模拟网络异常
            System.out.println(10/0);
            String sql1 = "update user_table set balance = balance+100 where user = ?";
            update(connection,sql1,"BB");
            //提交事务
            connection.commit();
            System.out.println("转账成功");
        } catch (Exception e) {
            e.printStackTrace();
            //出现异常，回滚操作
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            //恢复自动提交
            try {
                connection.setAutoCommit(true);
            } catch (SQLException e) {
                e.printStackTrace();
            }
            //关闭连接
            JDBCUtilsJiuBan.closeResource(connection,null);
        }
    }
    //针对于不同表的通用查询操作，返货一条记录----Version 2.0 考虑事务
    public <T> T getInstance(Class<T> clazz, Connection connection,String sql,Object ...args) {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //1 获取连接
            //2 预编译sql,返回preparedStatment实例
            preparedStatement = connection.prepareStatement(sql);
            //3 填充占位符
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i+1,args[i]);
            }
            //4 执行，返回ResultSet结果集
            resultSet = preparedStatement.executeQuery();
            //5 处理结果集
            //获取元数据
            ResultSetMetaData metaData = resultSet.getMetaData();
            //获取总列数
            int columnCount = metaData.getColumnCount();
            if (resultSet.next()){
                T t = clazz.newInstance();
                for (int i = 0; i < columnCount; i++) {
                    //获取列别名
                    String columnLabel = metaData.getColumnLabel(i + 1);
                    //获取列值
                    Object columnValue = resultSet.getObject(i + 1);
                    //通过反射，将clumnValue存储到t对象的columLabel属性中
                    Field field = clazz.getDeclaredField(columnLabel);
                    field.setAccessible(true);
                    field.set(t,columnValue);
                }
                return t;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //关闭资源
            JDBCUtilsJiuBan.closeResource(null,preparedStatement,resultSet);
        }
        return null;
    }
    //传入conncetion,让多条操作在一个事务中
    //可能多条操作中间有查询
    @Test
    public void testTransactionSelect() throws Exception{
        Connection connection = null;
        try {
            //获取连接
            connection = JDBCUtilsJiuBan.getConnection();
            //关闭自动提交
            connection.setAutoCommit(false);
            String sql = "update user_table set balance = balance-100 where user = ?";
            update(connection,sql,"AA");
            //模拟出现异常
            //System.out.println(10/0);
            String sql1 = "select user,password,balance from user_table";
            UserTable userTable = getInstance(UserTable.class, connection, sql1);
            System.out.println("userTable = " + userTable);
            String sql2 = "update user_table set balance = balance+100 where user = ?";
            update(connection,sql2,"BB");
            System.out.println("转账成功");
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            //回滚
            try {
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }finally {
            //关闭资源
            JDBCUtilsJiuBan.closeResource(connection,null);
        }


    }
}
